package org.prs.persistence;

import com.prs.util.PrsException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.prs.model.Photo;
import org.prs.model.UserLevel;
import org.prs.persistence.connector.MySQLDatabaseConnector;

public class UserLevelDatabasePersitenceManager {

    private Connection conn;
    private ResultSet result;

    public boolean insertUserLevel(UserLevel userLevel) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertUserLevel = null;
        try {
            insertUserLevel = conn.prepareStatement("insert into userlevel set "
                    + " name=?, description=?");

            insertUserLevel.setString(1, userLevel.getName());
            insertUserLevel.setString(2, userLevel.getDescription());
            insertUserLevel.executeUpdate();
        } catch (SQLException e) {
            inserted = false;
            throw new PrsException(e);
        }
        return inserted;
    }
//
    public boolean insertPhoto(Photo photo) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertChildPhoto = null;
        try {
            insertChildPhoto = conn.prepareStatement("insert into photo set "
                    + " datecreated=STR_TO_DATE(?,'%d/%m/%Y'), name=?, path=?, childid=?, "
                    + "creatorid=?");

            insertChildPhoto.setString(1, photo.getDateCreated());
            insertChildPhoto.setString(2, photo.getName());
            insertChildPhoto.setString(3, photo.getPath());
            insertChildPhoto.setInt(4, photo.getChildId());
            insertChildPhoto.setInt(5, photo.getCreatorId());
            insertChildPhoto.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

//    public boolean updateChild(Child child) {
//        boolean updated = true;
//        conn = MySQLDatabaseConnector.getConnection();
//        PreparedStatement updateChild = null;
//        try {
//            updateChild = conn.prepareStatement("update child set "
//                    + " firstname=?, lastname=?, dateofbirth=STR_TO_DATE(?,'%d/%m/%Y'), creatorid=?, "
//                    + " address=?, childcode=?, district=?, gender=?, "
//                    + "middlename=?, phonenumber=?, residence=?, schoolstageid=? where childid=?");
//
//            updateChild.setString(1, child.getFirstName());
//            updateChild.setString(2, child.getLastName());
//            updateChild.setString(3, child.getDateOfBirth());
//            updateChild.setInt(4, child.getCreatorId());
//            updateChild.setString(5, child.getAddress());
//            updateChild.setString(6, child.getChildCode());
//            updateChild.setString(7, child.getDistrict());
//            updateChild.setString(8, child.getGender());
//            updateChild.setString(9, child.getMiddleName());
//            updateChild.setString(10, child.getPhoneNumber());
//            updateChild.setString(11, child.getResidence());
//            updateChild.setInt(12, child.getSchoolStageId());
//            updateChild.setInt(13, child.getChildId());
//            updateChild.executeUpdate();
//        } catch (SQLException e) {
//            e.printStackTrace(System.out);
//            updated = false;
//        }
//        return updated;
//    };

    public UserLevel getUserLevelById(int id) {
        UserLevel level=new UserLevel();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectUserLevelById = null;
        try {
            selectUserLevelById= conn.prepareStatement("select * from userlevel where id=?");
            selectUserLevelById.setInt(1, id);
            result = selectUserLevelById.executeQuery();
            while (result.next()) {
                level = getUserLevel(result);
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return level;
    }

    public List<UserLevel> getAllUserLevels(int start, int size, String orderBy) {
        List<UserLevel> levels = new ArrayList<UserLevel>();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectUserLevels = null;
        try {
            selectUserLevels = conn.prepareStatement("select * from userlevel order by " + orderBy + " LIMIT ?,?");
            selectUserLevels.setInt(1, start);
            selectUserLevels.setInt(2, size);
            result = selectUserLevels.executeQuery();
            while (result.next()) {
                levels.add(getUserLevel(result));
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return levels;
    }

    public int getUserLevelCount() {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectUserLevelCount = null;
        try {
            selectUserLevelCount = conn.prepareStatement("select count(*) from userlevel");
            result = selectUserLevelCount.executeQuery();
            while (result.next()) {

                return result.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return 0;
    }
     private UserLevel getUserLevel(ResultSet result) {
        UserLevel level = new UserLevel();
        try {            
                level=new UserLevel();
                level.setId(result.getInt("id"));
                level.setName(result.getString("name"));
                level.setDescription(result.getString("description"));
        } catch (SQLException e) {
           throw new PrsException(e);
        }
        return level;
    }
}
